package com.company;

import java.sql.*;

public class ZSGC {
    private static String driver = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@localhost:1521:XE";
    private static String user="hr";
    private static String password="123456";
    public static final int k = 7;
    public static final int m = 6;
    public String[][] chazhao() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("select * from student");//准备执行SQL语句
        ResultSet rs = pst.executeQuery();
        int sample = 0;
        while (rs.next()) {
            sample++;
        }
        String[][] stu = new String[sample][k];
        rs = pst.executeQuery();
        for (int i = 0; rs.next(); i++) {
            for (int j = 0; j < k; j++) {
                stu[i][j] = rs.getString(j + 1);
            }
        }
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库


        return stu;
    }
    public String[] chazhao(String name) throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("select * from student where 姓名=? ");
        pst.setString(1, name);
        ResultSet rs = pst.executeQuery();
        String[] a = new String[7];
        while (rs.next()) {
            a[0] = rs.getString("学号");
            a[1] = rs.getString("姓名");
            a[2] = rs.getString("性别");
            a[3] = rs.getString("系别");
            a[4] = rs.getString("寝室");
            a[5] = rs.getString("班级");
            a[6] = rs.getString("年级");
        }
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
        return a;
    }
    public String[] chazhao1(String gongyu) throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("select * from buliding where 公寓号=? ");
        pst.setString(1, gongyu);
        ResultSet rs = pst.executeQuery();
        String[] a = new String[1];
        while (rs.next()) {
            a[0]=rs.getString("财产");
        }
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
        return a;
    }
    public String[][] chazhao(String[] STU ) throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("select * from dormitory where 系别=? or 班级=? or 年级=? ");//准备执行SQL语句
        pst.setString(1, STU[0]);
        pst.setString(2, STU[2]);
        pst.setString(3, STU[1]);
        ResultSet rs = pst.executeQuery();
        int sample = 0;
        while (rs.next()) {
            sample++;
        }
        String[][] stu = new String[sample][6];
        rs = pst.executeQuery();
        for (int i = 0; rs.next(); i++) {
            for (int j = 0; j < 6; j++) {
                stu[i][j] = rs.getString(j + 1);
            }
        }
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
        return stu;
        }

    public void zengjia(String[] stu)throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("insert into student values ('"+stu[0]+"','"+stu[1]+"','"+stu[2]+"','"+stu[7]+"','"+stu[5]+"','"+stu[4]+"','"+stu[6]+"')");//准备执行SQL语句
        ResultSet rs = pst.executeQuery();
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
    }
   public void xuigai(String[] STU)throws SQLException {
       Connection conn = DriverManager.getConnection(url, user, password);
       PreparedStatement pst = conn.prepareStatement("update student set 系别=? where 学号=?");
       pst.setString(1, STU[2]);
       pst.setString(2, STU[0]);
       ResultSet rs = pst.executeQuery();
       rs.close();//数据库先开后关
       pst.close();
       conn.close();//关闭数据库
   }
    public void xuigai2(String[] gongyu)throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        PreparedStatement pst = conn.prepareStatement("update buliding set 登记=? where 公寓号=?");
        pst.setString(1, gongyu[1]);
        pst.setString(2, gongyu[0]);
        ResultSet rs = pst.executeQuery();
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
    }
    public void zengjia1(String[] qinshi)throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);//连接数据库
        PreparedStatement pst = conn.prepareStatement("insert into dormitory values ('"+qinshi[1]+"','"+qinshi[0]+"','"+0+"','"+qinshi[2]+"','"+qinshi[3]+"','"+qinshi[4]+"')");//准备执行SQL语句
        ResultSet rs = pst.executeQuery();
        rs.close();//数据库先开后关
        pst.close();
        conn.close();//关闭数据库
    }
}
